package com.hellogood.yaohuo.server.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.server.mgr.factory.MgrFactory;
import com.hellogood.yaohuo.shared.data.CallDTO;
import com.hellogood.yaohuo.shared.data.Constants;
import com.hellogood.yaohuo.shared.entity.Form;
import com.hellogood.yaohuo.shared.entity.PsDetail;
import com.hellogood.yaohuo.shared.entity.PsForm;
import com.hellogood.yaohuo.shared.exception.DatabaseException;
import com.hellogood.yaohuo.shared.vo.MainFrame;

public class PsFormDAO extends FormDAO {

	Logger log = Logger.getLogger(this.getClass());

	@SuppressWarnings("unchecked")
	public List<Form> searchForm(String tableFormName, String billNoField, String dateField, String orgCode, String startDate, String endDate, String rz) {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT (SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName,(SELECT OrgName FROM " + hxDbName
				+ "..SubShop WHERE OrgCode = f.ShopCode) AS ShopName,f.*,f." + billNoField + " AS billNo,f." + dateField + " AS billDate FROM " + tableFormName + " f WHERE f.ShopCode = '" + orgCode
				+ "' AND cast(f." + dateField + " as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + startDate + "'),120) and cast(f." + dateField
				+ " as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + endDate + "'),120)";
		if (rz.equals(Constants.RUZHANG_CODE_YES)) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals(Constants.RUZHANG_CODE_NO)) {
			sql = sql + " AND rzDate = ''";
		}
		List<?> list = super.query(sql);
		return (List<Form>) list;
	}

	@SuppressWarnings("unchecked")
	public List<Form> searchFormByQuyu(String tableFormName, String billNoField, String dateField, String quyuCode, String startDate, String endDate, String rz) {
		String hxDbName = DBConnection.hxDbName;
		String userCode = MainFrame.currentUser.getStaffCode();
		String orgs = "";
		if (quyuCode == null) {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUser(userCode);
		} else {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUserQuyu(userCode, quyuCode);
		}

		String sql = "SELECT (SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName,(SELECT OrgName FROM " + hxDbName
				+ "..SubShop WHERE OrgCode = f.ShopCode) AS ShopName,f.*,f." + billNoField + " AS billNo,f." + dateField + " AS billDate FROM " + tableFormName + " f WHERE f.ShopCode IN (" + orgs
				+ ") AND cast(f." + dateField + " as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + startDate + "'),120) and cast(f." + dateField
				+ " as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + endDate + "'),120)";
		if (rz.equals(Constants.RUZHANG_CODE_YES)) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals(Constants.RUZHANG_CODE_NO)) {
			sql = sql + " AND rzDate = ''";
		}
		List<?> list = super.query(sql);
		return (List<Form>) list;
	}

	public Form getFormByBillNo(String tableFormName, String billNoField, String dateField, String billNo) {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT (SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName,(SELECT OrgName FROM " + hxDbName
				+ "..SubShop WHERE OrgCode = f.ShopCode) AS ShopName, f.*,f." + billNoField + " AS billNo,f." + dateField + " AS billDate FROM " + tableFormName + " f WHERE f." + billNoField + " = '"
				+ billNo + "'";
		List<?> list = super.query(sql);
		if (list != null && (!list.isEmpty())) {
			return (Form) list.get(0);
		}
		return null;
	}

	public String createPeisong(PsForm form, boolean isModify) {
		String result = "";
		List<String> sqls = new ArrayList<String>();
		if (isModify) {
			String sql = "DELETE FROM psForm WHERE PsBillNo = '" + form.getPsBillNo() + "'";
			sqls.add(sql);
			sql = "DELETE FROM psDetail WHERE PsBillNo = '" + form.getPsBillNo() + "'";
			sqls.add(sql);
		}

		String sql = "INSERT INTO PsForm(psBillNo,psDate,orgCode,shopCode,counts,cost,netCost,total,staffCode," + "staffName,rzDate,remark,createTime,psCost) VALUES('" + form.getPsBillNo()
				+ "',CONVERT(varchar(100), GETDATE(), 23),'" + form.getOrgCode() + "','" + form.getShopCode() + "'," + form.getCounts() + "," + form.getCost() + "," + form.getCost() + ","
				+ form.getTotal() + ",'" + form.getStaffCode() + "','" + form.getStaffName() + "','" + form.getRzDate() + "','" + form.getRemark() + "',getdate()," + form.getPsCost() + ")";
		sqls.add(sql);

		List<PsDetail> list = form.getList();
		if (list != null && (!list.isEmpty())) {
			for (PsDetail d : list) {
				sql = "INSERT INTO PsDetail(psBillNo,pluCode,pluName,barCode,bra,color,size_s,size_m,size_l,size_xl,size_xxl,size_xxxl,size_avg,sglCount,jPrice,netJPrice,"
						+ "psPrice,price,counts,cost,netCost,total,rzDate,remark,OrgCode,createTime,depCode,depName,psCost) VALUES('"
						+ d.getBillNo()
						+ "','"
						+ d.getPluCode()
						+ "','"
						+ d.getPluName()
						+ "','"
						+ d.getBarCode()
						+ "','"
						+ d.getBra()
						+ "','"
						+ d.getColor()
						+ "',"
						+ d.getSize_s()
						+ ","
						+ d.getSize_m()
						+ ","
						+ d.getSize_l()
						+ ","
						+ d.getSize_xl()
						+ ","
						+ d.getSize_xxl()
						+ ","
						+ d.getSize_xxxl()
						+ ","
						+ d.getSize_avg()
						+ ","
						+ d.getSglCount()
						+ ","
						+ d.getjPrice()
						+ ","
						+ d.getNetJPrice()
						+ ","
						+ d.getPsPrice()
						+ ","
						+ d.getPrice()
						+ ","
						+ d.getCounts()
						+ ","
						+ d.getCost()
						+ ","
						+ d.getNetCost()
						+ ","
						+ d.getTotal()
						+ ",'" + d.getRzDate() + "','" + d.getRemark() + "','" + d.getOrgCode() + "',getDate(),'" + d.getDepCode() + "','" + d.getDepName() + "'," + d.getPsCost() + ")";
				sqls.add(sql);
			}
		}

		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}

		return result;
	}

	public String createPeisongBatch(List<PsForm> formList, Set<String> billNos) {
		String result = "";
		List<String> sqls = new ArrayList<String>();

		for (PsForm form : formList) {
			String sql = "INSERT INTO PsForm(psBillNo,psDate,orgCode,shopCode,counts,cost,netCost,total,staffCode," + "staffName,rzDate,remark,createTime,psCost) VALUES('" + form.getPsBillNo()
					+ "',CONVERT(varchar(100), GETDATE(), 23),'" + form.getOrgCode() + "','" + form.getShopCode() + "'," + form.getCounts() + "," + form.getCost() + "," + form.getCost() + ","
					+ form.getTotal() + ",'" + form.getStaffCode() + "','" + form.getStaffName() + "','" + form.getRzDate() + "','" + form.getRemark() + "',getdate()," + form.getPsCost() + ")";
			sqls.add(sql);

			List<PsDetail> list = form.getList();
			if (list != null && (!list.isEmpty())) {
				for (PsDetail d : list) {
					sql = "INSERT INTO PsDetail(psBillNo,pluCode,pluName,barCode,bra,color,CSize,sglCount,jPrice,netJPrice,"
							+ "psPrice,price,counts,cost,netCost,total,rzDate,remark,OrgCode,psCost,depCode,depName,createTime) VALUES('"
							+ d.getBillNo()
							+ "','"
							+ d.getPluCode()
							+ "','"
							+ d.getPluName()
							+ "','"
							+ d.getBarCode()
							+ "','"
							+ d.getBra()
							+ "','"
							+ d.getColor()
							+ "','"
							+ d.getSize()
							+ "',"
							+ d.getSglCount()
							+ ","
							+ d.getjPrice()
							+ ","
							+ d.getNetJPrice()
							+ ","
							+ d.getPsPrice()
							+ ","
							+ d.getPrice()
							+ ","
							+ d.getCounts()
							+ ","
							+ d.getCost()
							+ ","
							+ d.getNetCost()
							+ ","
							+ d.getTotal()
							+ ",'"
							+ d.getRzDate()
							+ "','"
							+ d.getRemark()
							+ "','"
							+ d.getOrgCode()
							+ "',"
							+ d.getPsCost()
							+ ",'"
							+ d.getDepCode()
							+ "','"
							+ d.getDepName() + "',getDate())";
					sqls.add(sql);
				}
			}
		}
		if (billNos != null) {
			for (String bill : billNos) {
				String sql = "UPDATE quyuHuizongShenhe SET isHz = 1 WHERE billNo = '" + bill + "'";
				sqls.add(sql);
			}
		}
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			log.error(e.getMessage());
			e.printStackTrace();
			return e.getMessage();
		}

		return result;

	}

	public String rz(String bill) throws DatabaseException {
		String sql = "UPDATE psForm SET rzDate = CONVERT(VARCHAR(10),getDate(),120) WHERE psBillNo = '" + bill + "'";
		CallDTO dto = new CallDTO();
		String call = "{CALL psrz(?,?)}";
		// String call = "{EXEC psrz @psbillNo = '" + bill + "',@sysdb='" +
		// DBConnection.hxDbName + "'}";
		dto.setBill(bill);
		dto.setCall(call);
		dto.setSql(sql);
		dto.setHxdbName(DBConnection.hxDbName);
		return super.callProcedure(dto);
	}
}
